"""
Case Type   : 基础功能
Case Name   : 序列使用owned by关联表字段，序列和表不属于同一用户，合理报错
Create At   : 2023/05/08
Owner       : cr13
Description :
    1.创建用户并赋权
    2.新建用户建表
    3.初始用户创建序列，序列owned by关联表字段
    4.清理环境
Expect      :
    1.成功
    2.成功
    3.合理报错
    4.成功
History     :
"""
import os
import unittest
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import macro


class SQL(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.constant = Constant()
        self.comshpri = CommonSH('PrimaryDbUser')
        self.tb_name = "t_ddl_sequence_036"
        self.sequence = "s_ddl_sequence_036"
        self.user = "u_ddl_sequence_036"

    def test_create_sequence(self):
        text = '----step1:创建用户并赋权 expect:成功-----'
        self.log.info(text)
        sql_cmd = f"drop user if exists {self.user} cascade;create user " \
            f"{self.user} with password '{macro.COMMON_PASSWD}';" \
            f"grant create on schema public to {self.user};"
        result = self.comshpri.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, result, '执行失败' +
                      text)
        self.assertIn(self.constant.CREATE_ROLE_SUCCESS_MSG, result, '执行失败'
                      + text)
        self.assertIn(self.constant.GRANT_SUCCESS_MSG, result, '执行失败' +
                      text)

        text = '----step2:新建用户建表 expect:成功----'
        self.log.info(text)
        sql_cmd = f"set current_schema = public;drop table if exists " \
            f"{self.tb_name};create table {self.tb_name}(id int);"
        result = self.comshpri.execut_db_sql(
            sql_cmd, sql_type=f"-U {self.user} -W '{macro.COMMON_PASSWD}'")
        self.log.info(result)
        self.assertIn(self.constant.SET_SUCCESS_MSG, result, '执行失败' + text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result, '执行失败'
                      + text)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, result, '执行失败' +
                      text)

        text = '----step3:初始用户创建序列，序列owned by关联表字段 ' \
               'expect:合理报错----'
        self.log.info(text)
        sql_cmd = f"drop sequence if exists {self.sequence};create sequence " \
            f"{self.sequence} owned by {self.tb_name}.id;"
        result = self.comshpri.execut_db_sql(sql_cmd)
        self.log.info(result)
        self.assertIn('ERROR:  sequence must have same owner as table it is '
                      'linked to', result, '执行失败' + text)

    def tearDown(self):
        text = "----step4:环境清理 expect:成功----"
        self.log.info(text)
        result = self.comshpri.execut_db_sql(
            f"drop table if exists {self.tb_name};drop user if exists "
            f"{self.user} cascade;")
        self.log.info(result)
        self.assertIn(self.constant.DROP_ROLE_SUCCESS_MSG, result, '执行失败' +
                      text)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, result, '执行失败'
                      + text)
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
